package com.wyj.wuliwuli.utils;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * @Author wyj
 * @version 1.0
 * @description:
 */

public class ExcelUtils {
    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     *
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public static List getCourseListByExcel(InputStream in, String fileName) throws Exception {
        List list = new ArrayList<>();
        // 创建excel工作簿
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet == null) {
                continue;
            }
            // 滤过第一行标题
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || row.getFirstCellNum() == j) {
                    continue;
                }
                List<Object> li = new ArrayList<>();

                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    // 日期类型转换
                    if(y == 3) {
                        //cell.setCellType(CellType.STRING);
                        double s1 = cell.getNumericCellValue();
                        Date date = HSSFDateUtil.getJavaDate(s1);
                        li.add(date);
                        continue;
                    }
                    li.add(cell);
                }
                list.add(li);
            }
        }
        work.close();
        return list;
    }

    /**
     * 判断文件格式
     * @param in
     * @param fileName
     * @return
     */
    private static Workbook getWorkbook(InputStream in, String fileName) throws Exception {
        Workbook book = null;
        String filetype = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(filetype)) {
            book = new HSSFWorkbook(in);
        } else if (".xlsx".equals(filetype)) {
            book = new XSSFWorkbook(in);
        } else {
            throw new Exception("请上传excel文件！");
        }
        return book;
    }

    public static void export(XSSFWorkbook wb, HttpServletResponse response, String tableName) {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        InputStream is = null;
        try {
            wb.write(os);
            byte[] content = os.toByteArray();
            is = new ByteArrayInputStream(content);
            tableName = URLEncoder.encode(tableName, "UTF-8");
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + tableName + ".xlsx");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Access-Control-Allow-Origin", "*");
            ServletOutputStream out = response.getOutputStream();
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            response.flushBuffer();
        } catch (IOException e) {
            throw new RuntimeException("导出数据异常", e);
        } finally {
            try {
                bos.close();
                is.close();
                bis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static XSSFWorkbook initExcelWorkbook(List<String> firstRow) {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("fieldSheet");
        sheet.setAutobreaks(true);
        XSSFCellStyle setBorder = wb.createCellStyle();
//        setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        setBorder.setWrapText(true);
        XSSFRow row = sheet.createRow(0);

        //设置第一行的字段
        for (int i = 0; i < firstRow.size(); i++) {
            row.createCell(i).setCellValue(firstRow.get(i));
            sheet.autoSizeColumn(i);
        }
        return wb;
    }


    public static XSSFWorkbook exportExcel(List<LinkedHashMap> dataList, List<String> firstRow) {
        XSSFWorkbook wb = initExcelWorkbook(firstRow);
        XSSFSheet sheet = wb.getSheet("fieldSheet");
        for (int i = 0; i < dataList.size(); i++) {
            XSSFRow row = sheet.createRow(i + 1);
            LinkedHashMap<String, Object> data = dataList.get(i);
            for (int j = 0; j < firstRow.size(); j++) {
                String fieldName = firstRow.get(j);
                String value = "";
                if (data.get(fieldName) != null) {
                    value = String.valueOf(data.get(fieldName));
                }
                row.createCell(j).setCellValue(value);
            }
        }
        for (int i = 0; i < firstRow.size(); i++) {
            sheet.autoSizeColumn(i);
        }
        return wb;
    }
}
